﻿using CloudDcPc.Entity;
using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace CloudDcPc.Bean
{
    public class MyMenuBean
    {
        private MySqlConnection myConn = null;
        private DBConnection dbc = null;
        private MySqlCommand cmd = null;
        public MyMenuBean()
        {
            this.dbc = new DBConnection();
            this.myConn = this.dbc.getConnection();
        }

        public ObservableCollection<MyMenu> GetMyMenusByType(string type)
        {
            ObservableCollection<MyMenu> list = new ObservableCollection<MyMenu>();
            MyMenu myMenu = null;
            string sql = "select dm.*,dmt.name as xz from dc_menu dm,dc_menutype dmt where dm.menutype=dmt.id and type='"+type+"' order by position";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    myMenu = new MyMenu()
                    {
                        Id = (int)reader["id"],
                        MenuType = (int)reader["menutype"],
                        Food_num = reader["food_num"].ToString(),
                        Food_name = reader["food_name"].ToString(),
                        Price = (float)reader["price"],
                        Taste = reader["taste"].ToString(),
                        Type = reader["type"].ToString(),
                        Unit = reader["unit"].ToString(),
                        Enough = (int)reader["enough"],
                        Position = (int)reader["position"],
                        Print_pos = (int)reader["print_pos"],
                        Xz = reader["xz"].ToString(),
                        Suffix = reader["suffix"].ToString()
                    };
                    list.Add(myMenu);
                }

            }
            finally
            {
                dbc.close();
            }
            return list;
        }

        public List<string> GetAllType()
        {
            List<string> list = new List<string>();
            string sql = "select type from dc_menu group by type";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    list.Add(reader["type"].ToString());
                }

            }
            finally
            {
                dbc.close();
            }
            return list;
        }

        public bool HasMyMenu(string food_num)
        {
            string sql = "select id from dc_menu where food_num='" + food_num + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return true;
                }
                return false;
            }
            finally
            {
                dbc.close();
            }
        }

        public bool HasMyMenuByObj(MyMenu m)
        {
            string sql = "select id from dc_menu where (food_num='" + m.Food_num + "') and id!=" + m.Id;
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return true;
                }
                return false;
            }
            finally
            {
                dbc.close();
            }
        }

        public bool HasMyMenuByFoodName(string food_name)
        {
            string sql = "select id from dc_menu where food_name='" + food_name + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    return true;
                }
                return false;
            }
            finally
            {
                dbc.close();
            }
        }


        public bool DelMyMenuById(int id)
        {
            string sql = "delete from dc_menu where id='" + id + "'";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                dbc.close();
            }
        }


        public bool AddMyMenu(MyMenu menu)
        {
            string suffix = "'"+menu.Suffix+"'";
            if (menu.Suffix == "")
                suffix = "null";
                
            string sql = "insert into dc_menu(food_num,food_name,price,taste,type,unit,enough,position,print_pos,menutype,suffix) values('" + menu.Food_num + "','" + menu.Food_name + "','" + menu.Price + "','" + menu.Taste + "','" + menu.Type + "','" + menu.Unit + "','" + menu.Enough + "','" + menu.Position + "','" + menu.Print_pos + "','" + menu.MenuType + "'," + suffix + ")";
            string sql2 = "select * from dc_menu where type='" + menu.Type + "' and position>=" + menu.Position;
            string sql3 = "";

            if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
            MySqlTransaction myTran = myConn.BeginTransaction();
            try
            {
                cmd = new MySqlCommand(sql2, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();


                List<int> id_list = new List<int>();
                while (reader.Read())
                {
                    id_list.Add((int)reader["id"]);
                }
                reader.Close();   //先关闭才行！！

                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                int p = menu.Position;
                for (int i = 0; i < id_list.Count; i++)
                {
                    p++;
                    sql3 = "update dc_menu set position=" + p + " where id=" + id_list[i] + "";
                    cmd = new MySqlCommand(sql3, myConn);
                    cmd.ExecuteNonQuery();
                }
                myTran.Commit();
                return true;
            }
            catch
            {
                myTran.Rollback();
                return false;
            }
            finally
            {
                dbc.close();
            }
        }


        public bool UpdateMyMenu(MyMenu menu)
        {
            string suffix = "'" + menu.Suffix + "'";
            if (menu.Suffix == "")
                suffix = "null";
            string sql = "update dc_menu set print_pos='" + menu.Print_pos + "',food_num='" + menu.Food_num + "',food_name='" + menu.Food_name + "',price='" + menu.Price + "',taste='" + menu.Taste + "',type='" + menu.Type + "',unit='" + menu.Unit + "',enough='" + menu.Enough + "',position='" + menu.Position + "',menutype='" + menu.MenuType + "',suffix=" + suffix + " where id='" + menu.Id + "'";

            string sql2 = "select * from dc_menu where type='" + menu.Type + "' and position>=" + menu.Position;
            string sql3 = "";
            
            if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
            MySqlTransaction myTran = myConn.BeginTransaction();
            try
            {
                cmd = new MySqlCommand(sql2, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();


                List<int> id_list = new List<int>();
                while (reader.Read())
                {
                    if ((int)reader["id"] != menu.Id)
                        id_list.Add((int)reader["id"]);
                }
                reader.Close();   //先关闭才行！！

                cmd = new MySqlCommand(sql, myConn);
                cmd.ExecuteNonQuery();
                int p = menu.Position;
                for (int i = 0; i < id_list.Count; i++)
                {
                    p++;
                    sql3 = "update dc_menu set position=" + p + " where id=" + id_list[i] + "";
                    cmd = new MySqlCommand(sql3, myConn);
                    cmd.ExecuteNonQuery();
                }
                myTran.Commit();
                return true;
            }
            catch
            {
                myTran.Rollback();
                return false;
            }
            finally
            {
                dbc.close();
            }

        }


        public ObservableCollection<MyMenu> GetAllMyMenu()
        {
            ObservableCollection<MyMenu> list = new ObservableCollection<MyMenu>();
            MyMenu myMenu = null;
            string sql = "select dm.*,dmt.name as xz from dc_menu dm,dc_menutype dmt where dm.menutype=dmt.id order by type,position";
            try
            {
                if (myConn.State != System.Data.ConnectionState.Closed) System.Threading.Thread.Sleep(1000); myConn.Open();
                cmd = new MySqlCommand(sql, myConn);
                MySqlDataReader reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    myMenu = new MyMenu()
                    {
                        Id = (int)reader["id"],
                        Food_num = reader["food_num"].ToString(),
                        Food_name = reader["food_name"].ToString(),
                        Price = (float)reader["price"],
                        Taste = reader["taste"].ToString(),
                        Type = reader["type"].ToString(),
                        Unit = reader["unit"].ToString(),
                        Enough = (int)reader["enough"],
                        Position = (int)reader["position"],
                        Print_pos = (int)reader["print_pos"],
                        MenuType = (int)reader["menutype"],
                        Xz = reader["xz"].ToString(),
                        Suffix = reader["suffix"].ToString()
                    };
                    list.Add(myMenu);
                }

            }
            finally
            {
                dbc.close();
            }
            return list;
        }
    }
}
